''' 项目管理 '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo
import math
import openpyxl

logger = logging.getLogger('app')

# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'],cursorclass=pymysql.cursors.DictCursor)
    return pool.connection()


'''
项目档案页面（组） start
developing
'''
'''项目列表 start Finished by cdx'''
#获取项目信息数据
def get_project_data(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    pageCurrent = int(params['page'])
    pageSize = int(params['pageSize'])
    searchValue = params.get('searchValue','') #不一定存在的参数
    projcet_list = []
    result = {}
    try:
        # 构建长字符串常用的方式，这不是元组！
        base_sql = (r"SELECT id, DATE_FORMAT(date, '%%Y-%%m-%%d') AS format_date, name, address FROM projects")
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("projects.name LIKE %s")
            sql_params.append(f"%{searchValue}%")  # 进行模糊匹配，%是LIKE子句的一部分
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}"  # ' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            cur.execute(sql, sql_params)
        else:
            cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['projectId'] = row['id']
            row_dict['projectName'] = row['name']
            row_dict['projectAddress'] = row['address']
            projcet_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': projcet_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#删除项目信息数据
def del_project(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    projectId_list = params.get('delProjectList', '')
    result = {}
    try:
        placeholders = ', '.join(['%s'] * len(projectId_list))
        sql = (f"DELETE FROM projects WHERE id in ({placeholders})")
        cur.execute(sql, projectId_list)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'

        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#新建project
def add_project(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'projectName': 'name',
        'projectAddress': 'address',
    }
    projectName = params.get('projectName')
    result = {}
    try:
        sql = ("SELECT * FROM projects WHERE name = %s")
        cur.execute(sql, [projectName])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'项目 {projectName} 已存经'
            return result
        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if value:
                print(key,value)
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        sql = f"INSERT INTO projects({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增用户成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#project编辑页面初始化数据
def edit_project_init(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['projectID']
    result = {}
    # 前端field映射到后端数据库字段
    field_mapping = {
        'projectId': 'id',
        'projectName': 'name',
        'projectAddress': 'address',
    }
    try:
        sql = (f"SELECT * "
               f"FROM projects "
               f"WHERE id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        projectInfo = {}
        for field, value in field_mapping.items():
            projectInfo[field] = row[value]
        result['result'] = projectInfo
        result['code'] = 200
        result['message'] = 'ok'
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        print(result)
        cur.close()
        conn.close()
        return result

#更新project信息数据
def update_project(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'projectName': 'name',
        'projectAddress': 'address',
        'projectId': 'id',
    }
    result = {}
    try:
        sql = ("SELECT * FROM projects WHERE name = %s AND id != %s")
        cur.execute(sql, [params['projectName'],params['projectId']])
        print(f"判断重复：{sql,params['projectName']}")
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = '项目名重复'
            return result
        update_fields = []
        update_values = []
        for key, value in params.items():
            if value is not None:
                update_fields.append(f"{field_mapping[key]} = %s")
                update_values.append(value)
        sql = f"UPDATE projects SET {', '.join(update_fields)} WHERE id = %s"
        update_values.append(params['projectId'])
        print(sql, update_values)
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取班组信息列表
def get_group_data(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    reclist = []
    pageSize = param['pageSize']
    pageCurrent = param['pageCurrent']
    try:
        sql = " select gp.id,p.name as project_name,g.group_name,gp.groupleader,gp.groupstaffsize from groups_in_project gp left outer join projects p on p.id = gp.project_id left outer join project_groups g on g.group_id = gp.group_id where 1=1 "
        if 'selectProject' in param.keys():
            if param['selectProject'] is not None and param['selectProject'] != '':
                sql += " and p.name like '%" + param['selectProject'] + "%' "
        sql += " order by gp.id "

        cur.execute(sql)
        
        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            rowdict = {}
            rowdict['id'] = row['id']
            rowdict['project'] = row['project_name']
            rowdict['group'] = row['group_name']
            rowdict['groupleader'] = row['groupleader']
            rowdict['groupstaffsize'] = row['groupstaffsize']
            reclist.append(rowdict)
        data = {}
        data['list'] = reclist
        data['itemCount'] = 48
        data['page'] = pageCurrent
        data['pageSize'] = pageSize
        data['pageCount'] = pageCount
        data['itemCount'] = itemCount
        result['code'] = 200
        result['result'] = data
        result['message'] = 'ok'

    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取项目
def get_projectOptions():
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = " select id,name from projects group by id,name " 
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['value'] = row['name']
            rowdict['label'] = row['name']
            lisrec.append(rowdict)
        result['code'] = 200
        result['result'] = lisrec
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取班组
def get_groupOptions():
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = " select group_id,group_name from project_groups group by group_id,group_name " 
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['value'] = row['group_name']
            rowdict['label'] = row['group_name']
            lisrec.append(rowdict)
        result['code'] = 200
        result['result'] = lisrec
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#创建项目班组
def add_group(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        
        sql = "SELECT gp.id FROM groups_in_project gp left outer join projects p on p.id = gp.project_id left outer join project_groups g on g.group_id = gp.group_id where p.name like '%" \
              + params['project'] + "%' and g.group_name like '%" + params['group'] + "%' "
        cur.execute(sql)
        logger.info(sql)
        if len(cur.fetchall()) > 0:
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = '班组已存在，请重新输入'
            return result

        sql = "insert into groups_in_project(project_id,group_id,groupleader,groupstaffsize) values((select id from projects where name like '%%" + params['project'] + "%%' limit 1),(select group_id from project_groups where group_name like '%%" \
              + params['group'] + "%%' limit 1),%s,%s)"
        cur.execute(sql,(params['groupleader'],params['groupstaffsize']))

        result['code'] = 200
        result['message'] = '新增班组成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#更新项目班组
def update_group(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "update groups_in_project set groupleader = %s,groupstaffsize = %s where project_id = (select id from projects where name like '%%" + params['project'] + "%%' limit 1) and group_id = (select group_id from project_groups where group_name like '%%" \
              + params['group'] + "%%' limit 1) limit 1 "
        logger.info(sql)
        cur.execute(sql,(params['groupleader'],params['groupstaffsize']))

        result['code'] = 200
        result['message'] = '更新班组成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '更新失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#删除班组信息数据
def batchDelGroup(params: dict[str: list]):
    conn = connect_db()
    cur = conn.cursor()
    employeeId_list = params.get('delGroupList', '')
    result = {}
    try:
        placeholders = ', '.join(['%s']*len(employeeId_list))
        sql = (f"DELETE FROM groups_in_project WHERE id in ({placeholders})")
        logger.info(sql)
        logger.info(employeeId_list)
        cur.execute(sql, employeeId_list)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'

        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#批量导入班组信息数据
def import_group(ws):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        i = 2
        while True:
            if ws['A' + str(i)].value is not None:
                data = {}
                data['project'] = ws['A' + str(i)].value
                data['group'] = ws['B' + str(i)].value
                data['groupleader'] = ws['C' + str(i)].value
                data['groupstaffsize'] = ws['D' + str(i)].value

                sql = "insert into groups_in_project(project_id,group_id,groupleader,groupstaffsize) values((select id from projects where name like '%%" + data['project'] + "%%' limit 1),(select group_id from project_groups where group_name like '%%" \
                      + data['group'] + "%%' limit 1),%s,%s)"
                cur.execute(sql,(data['groupleader'],data['groupstaffsize']))
            else:
                break;
            i += 1
            
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = ''

        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '导入失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#批量导出班组信息数据
def getgroupexcel(dirpath, param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        # 打开导出数据模板
        wb = openpyxl.load_workbook(dirpath + '/班组信息.xlsx')
        ws = wb['班组信息']
        i = 2
        # 获取班组数据
        sql = " select gp.id,p.name as project_name,g.group_name,gp.groupleader,gp.groupstaffsize from groups_in_project gp left outer join projects p on p.id = gp.project_id left outer join project_groups g on g.group_id = gp.group_id where 1=1 "
        if 'selectProject' in param.keys():
            if param['selectProject'] is not None and param['selectProject'] != '':
                sql += " and p.name like '%" + param['selectProject'] + "%' "
        sql += " order by gp.id "

        cur.execute(sql)
        cur.execute(sql)
        for row in cur.fetchall():
            ws['A' + str(i)] = row['project_name']
            ws['B' + str(i)] = row['group_name']
            ws['C' + str(i)] = row['groupleader']
            ws['D' + str(i)] = row['groupstaffsize']
            i += 1

        wb.save(dirpath + '/export班组信息.xlsx')

        result['result'] = ''
        result['code'] = 200
        result['message'] = ''

    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '导出失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result


'''项目列表 end Finished by cdx'''
'''
项目档案页面（组） end
'''
